﻿

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using MySql.Data.MySqlClient;

public class DAL_tbl_countrystatemapping
{
    public DAL_tbl_countrystatemapping()
    {

    }



    public List<tbl_countrystatemapping> select(int RID)
    {
        List<tbl_countrystatemapping> list = new List<tbl_countrystatemapping>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select RID,Country,State,City from tbl_countrystatemapping where RID=@RID";
        cmd.Parameters.AddWithValue("@RID", RID);
        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_countrystatemapping()
            {
                RID = Convert.ToInt32(dr["RID"]),
                Country = dr["Country"].ToString(),
                State = dr["State"].ToString(),
                City = dr["City"].ToString()
            });
        }

        return list;
    }


    public List<tbl_countrystatemapping> selectCountry()
    {
        List<tbl_countrystatemapping> list = new List<tbl_countrystatemapping>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select distinct Country from tbl_countrystatemapping order by Country";
        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_countrystatemapping()
            {
                Country = dr["Country"].ToString()
            });
        }

        return list;
    }

    public List<tbl_countrystatemapping> selectState(string country)
    {
        List<tbl_countrystatemapping> list = new List<tbl_countrystatemapping>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select distinct State from tbl_countrystatemapping where Country=@Country order by State";
        cmd.Parameters.AddWithValue("@Country", country);
        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_countrystatemapping()
            {
                State = dr["State"].ToString()
            });
        }

        return list;
    }

    public List<tbl_countrystatemapping> selectCity(string country, string state)
    {
        List<tbl_countrystatemapping> list = new List<tbl_countrystatemapping>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select distinct City from tbl_countrystatemapping where Country=@Country and State=@State order by City";
        cmd.Parameters.AddWithValue("@Country", country);
        cmd.Parameters.AddWithValue("@State", state);
        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_countrystatemapping()
            {
                City = dr["City"].ToString()
            });
        }

        return list;
    }


    public List<tbl_countrystatemapping> selectAll()
    {
        List<tbl_countrystatemapping> list = new List<tbl_countrystatemapping>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select RID,Country,State,City from tbl_countrystatemapping Order By RID";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_countrystatemapping()
            {
                RID = Convert.ToInt32(dr["RID"]),
                Country = dr["Country"].ToString(),
                State = dr["State"].ToString(),
                City = dr["City"].ToString()
            });
        }

        return list;
    }



    public int insert(String Country, String State, String City)
    {
        int i = 0;
        string sql = @"Insert into tbl_countrystatemapping(Country, State, City)
                        values(@Country, @State, @City)";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@Country", Country);
        cmd.Parameters.AddWithValue("@State", State);
        cmd.Parameters.AddWithValue("@City", City);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



    public int update(int RID, String Country, String State, String City)
    {
        int i = 0;
        string sql = @"Update tbl_countrystatemapping SET Country=@Country, State=@State, City=@City WHERE RID=@RID";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@RID", RID);
        cmd.Parameters.AddWithValue("@Country", Country);
        cmd.Parameters.AddWithValue("@State", State);
        cmd.Parameters.AddWithValue("@City", City);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



    public int delete(int RID)
    {
        int i = 0;
        string sql = "Delete FROM tbl_countrystatemapping WHERE RID=@RID";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@RID", RID);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



}